//
//  DataBase.m
//  Lesson_UI_17_Sqlite
//
//  Created by dlios on 15/8/25.
//  Copyright (c) 2015年 dlios. All rights reserved.
//

#import "DataBase.h"

@implementation DataBase

+(DataBase *)shareDataBase
{
    
    static DataBase * dbHandle = nil ;
    if (dbHandle == nil) {
        dbHandle = [[DataBase alloc]init];
        
    }
    return dbHandle ;
    
}

static sqlite3 * db ; //创建一个数据库对象
#pragma mark - 打开数据库
-(void)openDB
{
    //获取沙河路径
    NSString * documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
   
     //拼接Sqlite文件路径
//    NSString * sqlitePath = [documentPath stringByAppendingString: @"preeee.sqlite"];
    
    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:@"preeee.sqlite"];
    NSLog(@"路径 = %@",sqlitePath);
    
    
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return ;
        
    }
    //打开数据库的方法
    int result = sqlite3_open(sqlitePath.UTF8String,&db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
        
    }
    else
    {
        NSLog(@"数据库打开失败");
        
    }
}

#pragma mark - 创建数据库表

-(void)createTable
{
    
    //生成创建表的sql语句
    
    NSString  * creatSql = @"CREATE TABLE IF NOT EXISTS lanou722(number INTEGER PRIMARY KEY AUTOINCREMENT ,dealerName TEXT , modelName TEXT , price TEXT , decline TEXT , phone TEXT , dealerNewsId TEXT , modelId TEXT)";
    
 
    //执行sql语句
    int result = sqlite3_exec(db, creatSql.UTF8String,NULL ,NULL , nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }
    else
    {
        NSLog(@"创建表失败");
        
    }
}

#pragma mark - 插入信息(增)

-(void)insertStudent:(PreferentialModel *)model
{
    
    //创建插入学生信息的sql语句
    NSString * insertSql = [NSString stringWithFormat: @"INSERT INTO lanou722(dealerName,modelName,price, decline , phone , dealerNewsId , modelId  )VALUES ('%@','%@','%@','%@','%@' , '%@' , '%@')",model.dealerName,model.modelName, model.price,model.decline,model.phone , model.dealerNewsId , model.modelId];
    
    //执行sql语句
    int result = sqlite3_exec(db, insertSql.UTF8String,
                              NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"插入信息成功");
    }

    else{
        NSLog(@"插入信息失败");
    }
}

#pragma mark - 更新信息(改)

-(void)updateStudent:(PreferentialModel *)model number:(NSInteger)number
{

    //创建更新学生信息的sql语句
    NSString * updateSql = [NSString stringWithFormat:@"UPDATE lanou722 SET dealerName = '%@',modelName = '%@', price = '%@' ,decline = '%@' , phone = '%@' , dealerNewsId = '%@' , model.modelId = '%@' WHERE number = '%ld'",model.dealerName , model.modelName , model.price ,model.decline,model.phone, model.dealerNewsId , model.modelId , (long)number];
    
//执行sql语句
    int result = sqlite3_exec(db, updateSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }
    else
    {
        NSLog(@"更新失败");
        
    }

}


#pragma mark - 删除信息(删)

-(void)deleteStudent : (NSString * )modelName
{
    
    //删除学生信息sql语句
    NSString * deleteSql = [NSString stringWithFormat:@"DELETE FROM lanou722 WHERE modelName = '%@'",modelName];
    //执行sql语句
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    else{
        NSLog(@"失败哦");
    }
}

#pragma mark - 查询信息(查)

-(NSMutableArray *)selectAll
{
    
    NSString * selectSql = @"SELECT * FROM lanou722" ;
    //创建数据库跟随指针,用来循环遍历数据库表的每行数据
    
    sqlite3_stmt * stmt = nil ;
    //参数1 : 数据库对象 参数2 : 查询语句 参数3 : 查询语句字数限制(-1为不限制)参数4 : 跟随指针对象 参数5 : nil
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放信息的大数组
    
    NSMutableArray * array = [[NSMutableArray alloc] init];
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备工作完成");
    
    //开始循环遍历查询数据库每一条数据
    
    while (sqlite3_step(stmt)== SQLITE_ROW) {
        //当跟随指针指向的行的数据符合你的查询条件,逐一取出每列数据
        int number = sqlite3_column_int (stmt , 0);
        const unsigned char * dealerName = sqlite3_column_text(stmt, 1);
        const unsigned char * modelName = sqlite3_column_text(stmt, 2);
        const unsigned char * price = sqlite3_column_text(stmt, 3);
        const unsigned char * decline = sqlite3_column_text(stmt, 4);
        const unsigned char * phone = sqlite3_column_text(stmt, 5);
        const unsigned char * dealerNewsId = sqlite3_column_text(stmt, 6) ;
        const unsigned char * modelId = sqlite3_column_text(stmt, 7) ;
        
        NSString * dealerString = [NSString stringWithUTF8String:(const char *)dealerName];
        NSString * modelNameString = [NSString stringWithUTF8String:(const char *)modelName];
        NSString * priceString = [NSString stringWithUTF8String:(const char *)price];
        NSString * declineString = [NSString stringWithUTF8String:
        (const char *)decline];
        
        NSString * phoneString = [NSString stringWithUTF8String:(const char *)phone];
        
        NSString * dealerNewsIdString = [NSString stringWithUTF8String:(const char *)dealerNewsId];
        
        NSString * modelIdString = [NSString stringWithUTF8String:(const char *)modelId];
        
        
     PreferentialModel * model = [[PreferentialModel alloc]init];
        
        model.dealerName = dealerString ;
          model.modelName = modelNameString ;
        model.price = priceString ;
        model.decline = declineString ;
        model.phone = phoneString ; 
        model.dealerNewsId = dealerNewsIdString ;
        
        model.modelId = modelIdString ;
        
        model.number = number ;
        
        //将学生对象存入大数组
        [array addObject:model];
    }
        
    }
    else
    {
        NSLog(@"查询失败");
    }
    
    return array ;
    
}

-(void)closeDB
{
    db = nil ;
    
    int result = sqlite3_close(db);
        if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
        db = nil ;
    }
    else{
        NSLog(@"失败");
    }
}


-(void)dropTable
{
    NSString * dropSql = @"DROP TABLE lanou722";
    int result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    else
    {
        NSLog(@"删除失败");
        
    }
}

@end
